package com.cn.jdia.springmvc.handlers;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.context.support.ClassPathXmlApplicationContext;

//bak 2017-08-21
//Data sync Thread
public class DataSyncThreadBak_20170821 extends Thread {

	public void run() {
		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource");

		while (true) {

			DateTime datetime = new DateTime();
			String nowDate = datetime.toString("YYYYMMdd");

			String nowTime = datetime.toString("HHmmss");
			System.out.println("Run Data Sync ! --" + nowTime + "--");

			// 测试完成后再打开，锁定时间段
			if ((nowTime.compareTo("090000") > 0) && (nowTime.compareTo("160000") < 0)) {
				try {
					Thread.sleep(5000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				System.out.println("超出考勤时间,sleep...");
				continue;
			}

			String yesDateWithLine = datetime.minusDays(1).toString("YYYY-MM-dd");
			String timestap = "";
			String yestap = "";

			timestap = datetime.minusSeconds(15).toString("YYYY-MM-dd HH:mm:ss");
			yestap = datetime.minusDays(1).toString("YYYYMMdd");

			try {
				List<Map<String, String>> list = new ArrayList<Map<String, String>>();

				Connection conn = ds.getConnection();
				Statement st = conn.createStatement();
				StringBuffer sb = new StringBuffer();
				sb.append(
						"select a.person_name,a.dept_name,a.event_card,a.event_time,b.phone from ac_event a,oc_person b ");
				sb.append(" where a.event_card=b.identity_id ");
				sb.append(" and a.receive_time>'").append(timestap).append("'");

				ResultSet rs = st.executeQuery(sb.toString());
				int count = 0;
				while (rs.next()) {
					Map<String, String> map = new HashMap<String, String>();
					map.put("name", rs.getString("person_name"));
					map.put("dept", rs.getString("dept_name"));
					map.put("phone", rs.getString("phone"));
					map.put("id", rs.getString("event_card"));
					map.put("checktime", rs.getString("event_time"));
					list.add(map);
					count++;
				}
				rs.close();

				// new record, insert or update
				if (count != 0) {
					Iterator<Map<String, String>> it = list.iterator();
					while (it.hasNext()) {
						Map<String, String> map = it.next();

						// 判断当日该部门是否已经签到，若签到过则更新，否则插入
						// turnover字段0签退，1签到
						// 如果昨天的1:放到今天的0
						// 如果没有昨天:放今天的1
						// 每次先判断有无替班人员 只需判断中途换班，其他情况不影响

						StringBuffer tmp = new StringBuffer();
						tmp.append("select * from jan_attence where check_date='").append(yestap)
								.append("' and person_name='").append(map.get("name")).append("'");
						System.out.println(tmp.toString());
						ResultSet yesQueryRs = st.executeQuery(tmp.toString());

						boolean hasAttence = false;
						while (yesQueryRs.next()) {
							int turnover = yesQueryRs.getInt("turnover");
							if (turnover == 1) {
								hasAttence = true;
								break;
							}
						}
						yesQueryRs.close();

						// turnover : 0
						if (hasAttence) {
							System.out.println("昨天值过班");

							/*
							 * 昨天值过班，今天15秒内连刷两次判定为同时交接班，连值，trunover=1，且此记录可以被覆盖
							 */
							String eventtime = map.get("checktime");
							DateTimeFormatter format = DateTimeFormat.forPattern("YYYY-MM-dd HH:mm:ss");
							DateTime dt = DateTime.parse(eventtime, format);
							String secAgo = dt.minusSeconds(16).toString("YYYY-MM-dd HH:mm:ss");

							StringBuffer isCon = new StringBuffer("select * from ac_event where event_time>'")
									.append(secAgo).append("' and event_time<'").append(eventtime)
									.append("' and event_card='").append(map.get("id")).append("'");
							System.out.println(isCon);
							
							ResultSet crs = st.executeQuery(isCon.toString());
							int ccount=0;
							while(crs.next()){
								ccount++;
							}
							crs.close();
							
							if(ccount==0){
								System.out.println("非连值情况");
								
							}else{
								System.out.println("可能为连值");
								/*判断今天是否已有接班人员，如有不做任何操作，否则填到接班人员*/
								StringBuffer sb3=new StringBuffer();
								sb3.append("select * from jan_attence where check_date='").append(nowDate).append("'")
									.append(" and person_dept='").append(map.get("dept")).append("' and turnover=1");
								ResultSet rs3 = st.executeQuery(sb3.toString());
								int count3=0;
								while(rs3.next()){
									count3++;
								}
								rs3.close();
								
								if(count3==0){
									//今天还没接班的
									
								}else{
									//今天已经有接班的了，什么都不做
								}
							}
							/* 
							 * end for continue duty 2017-08-21
							 */

							StringBuffer sql = new StringBuffer();
							sql.append("select * from jan_attence where check_date='").append(nowDate).append("'")
									.append(" and person_dept='").append(map.get("dept")).append("' and turnover=0");

							ResultSet rs2 = st.executeQuery(sql.toString());
							String check_id = "";
							while (rs2.next()) {
								// primary key
								check_id = rs2.getString("check_id");
							}
							rs2.close();

							String update = "";
							if (!"".equals(check_id)) {// not null ,update
								update = "update jan_attence set person_name='" + map.get("name") + "',person_phone='"
										+ map.get("phone") + "',check_time='" + map.get("checktime").substring(11)
										+ "',person_id='" + map.get("id") + "', is_valid=false where check_id='"
										+ check_id + "'";
								st.execute(update);
								// boolean res = st.execute(update);
							} else {// null, insert with new check_id
								String idsql = "select max(check_id) max from jan_attence";
								ResultSet rs3 = st.executeQuery(idsql);
								String id = "";
								while (rs3.next()) {
									id = rs3.getString("max");
								}
								System.out.println("id:" + id);
								int pkid = Integer.parseInt(id);
								pkid++;
								rs3.close();

								update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
										+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
										+ map.get("dept") + "','" + map.get("checktime").substring(11) + "',false,'"
										+ pkid + "','" + map.get("id") + "',0)";
								System.out.println(update);
								boolean res = st.execute(update);
								System.out.println("insert:" + res);
							}

							// end for hasAttence
						} else { // turnover : 1
							System.out.println("昨天没值过班");

							// 需要考虑中途换班情况
							String dept = map.get("dept");
							StringBuffer agentSql = new StringBuffer();
							agentSql.append(
									"select a.agent_department_name dept,a.attendance_time dt,a.agent_id_card,(select name agent_name from oc_person where identity_id=a.agent_id_card),a.create_id_card,(select name create_name from oc_person where identity_id=a.create_id_card) ")
									.append(" from tm_sms_attendance_agent a").append(" where a.attendance_time = '")
									.append(yesDateWithLine).append(" 00:00:00'")
									.append(" and a.gmt_modified>a.attendance_time and a.agent_department_name='")
									.append(dept).append("'");
							// System.out.println(agentSql);

							ResultSet agentSt = st.executeQuery(agentSql.toString());
							int agCount = 0;
							String agTime = "";
							String agentIdCard = "";
							String agentName = "";
							String createIdCart = "";
							String creatrName = "";
							while (agentSt.next()) {
								agTime = agentSt.getString("dt");
								agentIdCard = agentSt.getString("agent_id_card");
								agentName = agentSt.getString("agent_name");
								createIdCart = agentSt.getString("create_id_card");
								creatrName = agentSt.getString("create_name");
								agCount++;
							}
							agentSt.close();

							// 无替换班
							if (agCount == 0) {
								System.out.println("there is no aggent record");

								System.out.println("这人是来接班的");

								StringBuffer sql = new StringBuffer();
								sql.append("select * from jan_attence where check_date='").append(nowDate).append("'")
										.append(" and person_dept='").append(dept).append("' and turnover=1");

								ResultSet rs2 = st.executeQuery(sql.toString());
								String check_id = "";
								while (rs2.next()) {
									// primary key
									check_id = rs2.getString("check_id");
								}
								rs2.close();

								String update = "";
								if (!"".equals(check_id)) {// not null
															// ,update
									update = "update jan_attence set person_name='" + map.get("name")
											+ "',person_phone='" + map.get("phone") + "',check_time='"
											+ map.get("checktime").substring(11) + "',person_id='" + map.get("id")
											+ "', is_valid=false where check_id='" + check_id + "'";
									st.execute(update);
									// boolean res = st.execute(update);
								} else {// null, insert with new check_id
									String idsql = "select max(check_id) max from jan_attence";
									ResultSet rs3 = st.executeQuery(idsql);
									String id = "";
									while (rs3.next()) {
										id = rs3.getString("max");
									}
									System.out.println("id:" + id);
									int pkid = Integer.parseInt(id);
									pkid++;
									rs3.close();

									update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
											+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
											+ map.get("dept") + "','" + map.get("checktime").substring(11) + "',false,'"
											+ pkid + "','" + map.get("id") + "',1)";
									System.out.println(update);
									boolean res = st.execute(update);
									System.out.println("insert:" + res);
								}

							} else {
								System.out.println(agTime + " " + agentIdCard + " " + agentName + " " + createIdCart
										+ "" + creatrName);
								if (agentName.equals(map.get("name"))) {
									System.out.println("这人是来交班的");

									StringBuffer sql = new StringBuffer();
									sql.append("select * from jan_attence where check_date='").append(nowDate)
											.append("'").append(" and person_dept='").append(map.get("dept"))
											.append("' and turnover=0");

									ResultSet rs2 = st.executeQuery(sql.toString());
									String check_id = "";
									while (rs2.next()) {
										// primary key
										check_id = rs2.getString("check_id");
									}
									rs2.close();

									String update = "";
									if (!"".equals(check_id)) {// not null
																// ,update
										update = "update jan_attence set person_name='" + map.get("name")
												+ "',person_phone='" + map.get("phone") + "',check_time='"
												+ map.get("checktime").substring(11) + "',person_id='" + map.get("id")
												+ "', is_valid=false where check_id='" + check_id + "'";
										st.execute(update);
										// boolean res = st.execute(update);
									} else {// null, insert with new check_id
										String idsql = "select max(check_id) max from jan_attence";
										ResultSet rs3 = st.executeQuery(idsql);
										String id = "";
										while (rs3.next()) {
											id = rs3.getString("max");
										}
										System.out.println("id:" + id);
										int pkid = Integer.parseInt(id);
										pkid++;
										rs3.close();

										update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
												+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
												+ map.get("dept") + "','" + map.get("checktime").substring(11)
												+ "',false,'" + pkid + "','" + map.get("id") + "',0)";
										System.out.println(update);
										boolean res = st.execute(update);
										System.out.println("insert:" + res);
									}

								} else {
									System.out.println("这人是来接班的");

									StringBuffer sql = new StringBuffer();
									sql.append("select * from jan_attence where check_date='").append(nowDate)
											.append("'").append(" and person_dept='").append(dept)
											.append("' and turnover=1");

									ResultSet rs2 = st.executeQuery(sql.toString());
									String check_id = "";
									while (rs2.next()) {
										// primary key
										check_id = rs2.getString("check_id");
									}
									rs2.close();

									String update = "";
									if (!"".equals(check_id)) {// not null
																// ,update
										update = "update jan_attence set person_name='" + map.get("name")
												+ "',person_phone='" + map.get("phone") + "',check_time='"
												+ map.get("checktime").substring(11) + "',person_id='" + map.get("id")
												+ "', is_valid=false where check_id='" + check_id + "'";
										st.execute(update);
										// boolean res = st.execute(update);
									} else {// null, insert with new check_id
										String idsql = "select max(check_id) max from jan_attence";
										ResultSet rs3 = st.executeQuery(idsql);
										String id = "";
										while (rs3.next()) {
											id = rs3.getString("max");
										}
										System.out.println("id:" + id);
										int pkid = Integer.parseInt(id);
										pkid++;
										rs3.close();

										update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
												+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
												+ map.get("dept") + "','" + map.get("checktime").substring(11)
												+ "',false,'" + pkid + "','" + map.get("id") + "',1)";
										System.out.println(update);
										boolean res = st.execute(update);
										System.out.println("insert:" + res);
									}

								}
							}

						}

					}
				}

				st.close();
				conn.close();

				Thread.sleep(5000);
			} catch (InterruptedException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}
